/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.DependenciaVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class DependenciaDao extends DBase {

    Connection conn = null;

    public DependenciaDao()  {
        super();
    }

    private void setDependenncia(DependenciaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDEPEN_PK(rs.getInt("NUM_CODDEPEN_PK"));
        bean.setNUM_TECHOPDEPEN(rs.getDouble("NUM_TECHOPDEPEN"));
        bean.setTX_DESCDEPEN(rs.getString("TX_DESCDEPEN"));
        bean.setNUM_PRESUDEPEN(rs.getBigDecimal("NUM_PRESUDEPEN"));
        bean.setResta(rs.getBigDecimal("resta"));

    }


    private void setDependennciaM(DependenciaVO bean, ResultSet rs) throws SQLException {

        bean.setNUM_CODOBJ_PK(rs.getInt("NUM_CODOBJ_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getInt("NUM_CODOBJESP_PK"));
        
        bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
        bean.setNUM_CODACTPERM_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_DESCACTPERM(rs.getString("TX_DESCACTPER"));
        bean.setTX_UMACTPERM(rs.getString("TX_UMACTPER"));
        bean.setNUM_CODMETAAPERM_PK(rs.getInt("NUM_CODMETAAPER_PK"));
        bean.setNUM_CANTACTPERM(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPERM(rs.getString("TX_TIPOACTPER"));
        bean.setNUM_CODDEPEN_PK(rs.getInt("NUM_CODDEPEN_PK"));
        bean.setNUM_PRESUDEPEN(rs.getBigDecimal("NUM_PRESUDEPEN"));
        bean.setNUM_TECHOPDEPEN(rs.getDouble("NUM_TECHOPDEPEN"));
        bean.setResta(rs.getBigDecimal("resta"));
        bean.setNUM_PRESUACTPERM(rs.getBigDecimal("NUM_PRESUACTPER"));

    }


     private void setDependennciaCombo(DependenciaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDEPEN_PK(rs.getInt("NUM_CODDEPEN_PK"));

        bean.setTX_DESCDEPEN(rs.getString("TX_DESCDEPEN"));
    }
     
     
     private void setSuma(DependenciaVO bean, ResultSet rs) throws SQLException {
        bean.setSuma(rs.getDouble("suma"));
    }


     private void setSumaM(DependenciaVO bean, ResultSet rs) throws SQLException {
        bean.setSumam(rs.getDouble("sumap"));
    }




     public double suma() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        String sql = "";
        double max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select sum(NUM_TECHOPDEPEN) as suma from ME_DEPENDENCIA";
            rs = stmt.executeQuery(sql);
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setSuma(bean, rs);
                max=bean.getSuma();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }


     public double sumaM(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        double max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select sum(NUM_PRESUACTPER) as sumap from ME_ACTPER where NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setSumaM(bean, rs);
                max=bean.getSumam();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }




    public String spMan(int tipo, DependenciaVO bean) {
     
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_DEPENDENCIA(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODDEPEN_PK());
            cs.setString(3, bean.getTX_DESCDEPEN());
            cs.setDouble(4, bean.getNUM_TECHOPDEPEN());

            cs.execute();



        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }


    public String spManM(int tipo, DependenciaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_DEPENDENCIAO(?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODACTPERM_PK());
            cs.setBigDecimal(3, bean.getNUM_PRESUACTPERM());
            cs.setInt(4, bean.getNUM_CODMETAAPERM_PK());
            cs.setInt(5, bean.getNUM_CANTACTPERM());
            cs.setString(6, bean.getTX_UMACTPERM());
            cs.setString(7, bean.getTX_TIPOACTPERM());
            cs.execute();



        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public ArrayList<DependenciaVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN from ME_DEPENDENCIA";
            rs = stmt.executeQuery(sql);
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependenncia(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select distinct ME_DEPENDENCIA.NUM_CODDEPEN_PK,ME_DEPENDENCIA.TX_DESCDEPEN,ME_DEPENDENCIA.NUM_TECHOPDEPEN,isnull(sum(ME_ACTPER.NUM_PRESUACTPER),0) AS NUM_PRESUDEPEN,isnull((sum(ME_ACTPER.NUM_PRESUACTPER)-ME_DEPENDENCIA.NUM_TECHOPDEPEN),0) as resta from ME_DEPENDENCIA inner join ME_ACTPER on ME_ACTPER.NUM_CODDEPEN_PK=ME_DEPENDENCIA.NUM_CODDEPEN_PK GROUP BY ME_DEPENDENCIA.NUM_CODDEPEN_PK,ME_DEPENDENCIA.TX_DESCDEPEN,ME_DEPENDENCIA.NUM_TECHOPDEPEN,(NUM_PRESUDEPEN-ME_DEPENDENCIA.NUM_TECHOPDEPEN) order by ME_DEPENDENCIA.NUM_CODDEPEN_PK";
            rs = stmt.executeQuery(sql);
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependenncia(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }






    public List getlista(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN,NUM_PRESUDEPEN,NUM_TECHOPDEPEN, (NUM_PRESUDEPEN-NUM_TECHOPDEPEN) as resta from ME_DEPENDENCIA where NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependenncia(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public double buscarpresupuesto(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        double resultado=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN,NUM_PRESUDEPEN,NUM_TECHOPDEPEN, (NUM_PRESUDEPEN-NUM_TECHOPDEPEN) as resta from ME_DEPENDENCIA where NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependenncia(bean, rs);
                resultado=bean.getNUM_TECHOPDEPEN();
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return resultado;
    }



    public List getlistaM(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select o.NUM_CODOBJ_PK, o.TX_DESCOBJ,oe.NUM_CODOBJESP_PK,oe.TX_DESCOBJESP,ap.NUM_CODACTPER_PK,ap.TX_DESCACTPER,ap.NUM_PRESUACTPER,isnull(map.TX_UMACTPER,'') as TX_UMACTPER,map.NUM_CODMETAAPER_PK,isnull(map.NUM_CANTACTPER,'') as NUM_CANTACTPER,isnull(map.TX_TIPOACTPER,'') as TX_TIPOACTPER,d.NUM_CODDEPEN_PK,d.NUM_PRESUDEPEN,d.NUM_TECHOPDEPEN, (ap.NUM_PRESUACTPER-d.NUM_TECHOPDEPEN) as resta from ME_DEPENDENCIA d inner join ME_ACTPER ap on ap.NUM_CODDEPEN_PK=d.NUM_CODDEPEN_PK full outer join ME_OBJESP oe on oe.NUM_CODOBJESP_PK=ap.NUM_CODOBJESP_PK inner join ME_OBJ o on o.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK inner join ME_METAAPER map on map.NUM_CODACTPER_PK=ap.NUM_CODACTPER_PK where d.NUM_CODDEPEN_PK=? order by NUM_CODOBJ_PK,NUM_CODOBJESP_PK,NUM_CODACTPER_PK,NUM_CODMETAAPER_PK;";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependennciaM(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public DependenciaVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        DependenciaVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN from ME_DEPENDENCIA where NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new DependenciaVO();
            if (rs.next()) {
                setDependenncia(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }
      public List getCombo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DependenciaVO> lista = new ArrayList<DependenciaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODDEPEN_PK,TX_DESCDEPEN from ME_DEPENDENCIA";
            rs = stmt.executeQuery(sql);
            DependenciaVO bean = null;
            while (rs.next()) {
                bean = new DependenciaVO();
                setDependennciaCombo(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
}
